ALTER        proc sp_yield_report
 -- Input parameter is process StepID;
 @process_step_id bigint,
 @process_step_type varchar(20),
 @lot_number varchar(50),
 @date_From varchar(20),
 @date_To   varchar(20)
 as
 
 begin
 /*variable to store the values
 */
 	
 	declare @begin_DT datetime, @end_DT datetime
 	if @date_From <> '0'
 	begin
 		set @begin_DT = convert(datetime, @date_From )
 	end
 	if @date_To  <> '0'
 	begin
 		set @end_DT = convert(datetime, @date_To)
 	end
 
 
 	declare @qty_in int, @qty_out int,  @lot_id bigint, @die_wafer int
 	declare @lot_no varchar(50), @prod_name varchar(50), @location varchar(50)
 	declare @yield float
 	declare @date_in datetime, @date_out datetime
 	declare @step_id bigint
 	declare @gross_die int
 	declare @stage varchar(50)
 	declare @ps_type_name varchar(20)

	declare @vendor_lot varchar(1000)
 	
 	declare @results Table(processStepID bigint, LotNumber varchar(50), ProductName varchar(50), DateIn datetime, DateOut datetime, QtyIn int, QtyOut int, Yield float, GrossDie int, Stage varchar(50), VendorLoc varchar(50),ps_type_name varchar(20),Vendor_Lot varchar(1000) )
 
 	declare @result Table(ProcessStep_ID bigint, 
 			PS_Type varchar(50), 
 			Process_Type_Name varchar(20),
 			Product_Name varchar(50),Product_ID int)	
 	if @process_step_id = 0
 	begin
 		/*with the current process step ID the procedure will not work becuz,
 		* the processstep_id sent would be a finished goods id and here we donot have
 		* that id in the result set
 		* the solutin is pass on the part number as varchar into the proc. we need to make changes in the jsp so that product
 		* name is sent instead of the step id. so change the lable value bean property to manufacturingPartNumber instead
 		* of the processStepId. This will take care of the problem.
 		*/
 		/*
 		if @process_step_id <>0
 			select * from fn_yield_all() where ProcessStepID = @process_step_id order by Stage
 		else
 		*/
 		if @process_step_type = 'FAB' AND @process_step_id = '0' and @lot_number = '0' AND @date_From = '0' and @date_To= '0'
 		begin
 
 		select * from fn_yield_all() where Stage=@process_step_type 
 		
 		end
 		if @process_step_type = 'FAB' AND @process_step_id = '0' and @lot_number <> '0' AND @date_From = '0' and @date_To= '0'
 		begin
 			select * from fn_yield_all() where Stage=@process_step_type and LotNumber=@lot_number 
 			  
 		end
 		if @process_step_type = 'FAB' AND @process_step_id = '0' and @lot_number <> '0' AND @date_From <> '0' and @date_To= '0'
 		begin
 			select * from fn_yield_all() where Stage=@process_step_type and LotNumber=@lot_number and  DateOut >=@begin_DT 
 			
 		end
 		if @process_step_type = 'FAB' AND @process_step_id = '0' and @lot_number <> '0' AND @date_From = '0' and @date_To<> '0'
 		begin
 			select * from fn_yield_all() where Stage=@process_step_type and LotNumber=@lot_number and  DateOut <=@end_DT 
 			
 		end
 		if @process_step_type = 'FAB' AND @process_step_id = '0' and @lot_number <> '0' AND @date_From <> '0' and @date_To<> '0'
 		begin
 			 select * from fn_yield_all() where Stage=@process_step_type and LotNumber=@lot_number and  DateOut >=@begin_DT and  DateOut <=@end_DT  
 			
 		end
 		if @process_step_type = 'FAB' AND @process_step_id = '0' and @lot_number = '0' AND @date_From <> '0' and @date_To<> '0'
 		begin
 			 select * from fn_yield_all() where Stage=@process_step_type and  DateOut >=@begin_DT and  DateOut <=@end_DT  
 			
 		end
 
 		if @process_step_type = 'FAB' AND @process_step_id = '0' and @lot_number='0' AND @date_From = '0' and @date_To<> '0'
 		begin
 			 select * from fn_yield_all() where Stage=@process_step_type and  DateOut <=@end_DT 
 			
 		end	
 		if @process_step_type = 'FAB' AND @process_step_id = '0' and @lot_number='0' AND @date_From <> '0' and @date_To= '0'
 		begin
 			select * from fn_yield_all() where Stage=@process_step_type and  DateOut >=@begin_DT 
 			
 		end
 
 
 		/*if @date_From = '0' and @date_To= '0'
 			select * from fn_yield_all() order by Stage
 		else if @date_From <>'0' and @date_To = '0'
 			select * from fn_yield_all() where DateOut >=@date_From 
 		else if @date_From ='0' and @date_To <> '0'
 			select * from fn_yield_all() where DateOut <=@date_To 
 		else
 			select * from fn_yield_all() where DateOut >=@date_From and DateOut <=@date_To */
 	end
 	if @process_step_type = 'all'
 	begin
 		
 		
 		/*with the current process step ID the procedure will not work becuz,
 		* the processstep_id sent would be a finished goods id and here we donot have
 		* that id in the result set
 		* the solutin is pass on the part number as varchar into the proc. we need to make changes in the jsp so that product
 		* name is sent instead of the step id. so change the lable value bean property to manufacturingPartNumber instead
 		* of the processStepId. This will take care of the problem.
 		*/
 		/*
 		if @process_step_id <>0
 			select * from fn_yield_all() where ProcessStepID = @process_step_id order by Stage
 		else
 		*/
 				
 
 		if @process_step_id = 0 and  @lot_number = '0' and @date_From = '0' and @date_To= '0'
 			select * from fn_yield_all()  order by Stage
 		else if @process_step_id = 0 and  @lot_number <> '0' and @date_From = '0' and @date_To= '0'
 			select * from fn_yield_all() where LotNumber=@lot_number  order by Stage
 		else if @process_step_id = 0 and @lot_number <> '0' and @date_From <> '0' and @date_To= '0'
 			select * from fn_yield_all() where  LotNumber=@lot_number and DateOut >=@begin_DT  order by Stage
 		else if @process_step_id = 0 and @lot_number <> '0' and @date_From = '0' and @date_To <> '0'
 			select * from fn_yield_all() where LotNumber=@lot_number and  DateOut <=@end_DT
 		else if @process_step_id = 0 and @lot_number <> '0' and @date_From <> '0' and @date_To <> '0'
 			select * from fn_yield_all() where LotNumber=@lot_number and DateOut>=@begin_DT and DateOut<=@end_DT
 		else if @process_step_id = 0 and @lot_number = '0' and @date_From <> '0' and @date_To <> '0'
 			select * from fn_yield_all() where  DateOut>=@begin_DT and DateOut<=@end_DT
 		else if @process_step_id = 0 and @lot_number = '0' and @date_From <> '0' and @date_To = '0'
 			select * from fn_yield_all() where DateOut >=@begin_DT
 		else if @process_step_id = 0 and @date_From ='0' and @date_From = '0' and @date_To <> '0'
 			select * from fn_yield_all() where DateOut <=@end_DT
 			
 		else if @process_step_id <> 0 and  @lot_number <> '0' and @date_From = '0' and @date_To= '0'
 			select * from fn_yield_all() where ProcessStepID in(select processStep_id from fn_get_process_flow_by_processstep_id(@process_step_id)) and LotNumber=@lot_number order by Stage
 		else if @process_step_id <> 0 and @lot_number <> '0' and @date_From <> '0' and @date_To= '0'
 			select * from fn_yield_all() where  ProcessStepID in(select processStep_id from fn_get_process_flow_by_processstep_id(@process_step_id)) and LotNumber=@lot_number and DateOut >=@begin_DT  order by Stage
 		else if @process_step_id <> 0 and @lot_number <> '0' and @date_From = '0' and @date_To <> '0'
 			select * from fn_yield_all() where ProcessStepID in(select processStep_id from fn_get_process_flow_by_processstep_id(@process_step_id)) and LotNumber=@lot_number and  DateOut <=@end_DT
 		else if @process_step_id <> 0 and @lot_number <> '0' and @date_From <> '0' and @date_To <> '0'
 			select * from fn_yield_all() where ProcessStepID in(select processStep_id from fn_get_process_flow_by_processstep_id(@process_step_id)) and LotNumber=@lot_number and DateOut>=@begin_DT and DateOut<=@end_DT
 
 		else if @process_step_id <> 0 and @lot_number = '0' and @date_From <> '0' and @date_To <> '0'
 			select * from fn_yield_all() where ProcessStepID in(select processStep_id from fn_get_process_flow_by_processstep_id(@process_step_id)) and DateOut>=@begin_DT and DateOut<=@end_DT
 		else if @process_step_id <> 0 and @lot_number = '0' and @date_From <> '0' and @date_To = '0'
 			select * from fn_yield_all() where ProcessStepID in(select processStep_id from fn_get_process_flow_by_processstep_id(@process_step_id)) and DateOut >=@begin_DT
 		else if @process_step_id <> 0 and @date_From ='0' and @date_From = '0' and @date_To <> '0'
 			select * from fn_yield_all() where ProcessStepID in(select processStep_id from fn_get_process_flow_by_processstep_id(@process_step_id))and DateOut <=@end_DT
 		else if @process_step_id <> 0 and @lot_number ='0' and @date_From = '0' and @date_To = '0'
 			select * from fn_yield_all() where ProcessStepID in(select processStep_id from fn_get_process_flow_by_processstep_id(@process_step_id)) 
 		
 
 	end
 	else if @process_step_type = 'fab'
 	begin
 		
 		if @process_step_id <> 0
 		begin
 
 			declare fabLotCursor1 cursor for
 			select Lot_ID  from lot_table where ProcessStep_Id= @process_step_id
 			open fabLotCursor1
 			fetch next from fabLotCursor1 into @lot_id
 			while @@fetch_status = 0
 			begin
 				declare fabLotCursor cursor for
 				select Lot_ID, FL_Vendor_Lot_Number, FL_Date_In, FL_Date_Out, FL_Input_Quantity, FL_Output_Quantity, FL_Yield, FL_GROSS_DIE_WAFER from Fab_Lot_History where Lot_ID=@lot_id and FL_Current_Status = 'close'
 
 				open fabLotCursor
 				fetch next from fabLotCursor into @lot_id, @vendor_lot, @date_in, @date_out, @qty_in, @qty_out, @yield, @gross_die
 				while @@fetch_status = 0
 				begin
 					
					select @lot_no = Lot_Number from Lot_table where Lot_ID = @lot_id
					
 					--select  @lot_no=FL_Vendor_Lot_Number, @date_in=FL_Date_In,@date_out= FL_Date_Out,@qty_in=FL_Input_Quantity,@qty_out=FL_Output_Quantity,@yield=FL_Yield,@gross_die=FL_GROSS_DIE_WAFER from Fab_Lot_History where Lot_ID=@lot_id and  FL_Current_Status = 'close'
 					select @prod_name =part_Number from product where processStep_ID = @process_step_id
 					select @stage = ps_type,@process_step_type = ps_type_name from process_step where processStep_ID = @process_step_id
 					select @location = (select L_Vendor_Name from Location,Lot_Table where Lot_id=@lot_id and lot_table.location_Id = location.location_Id)
					
 					insert into @results values(@process_step_id, @lot_no, @prod_name, @date_in, @date_out, @qty_in, @qty_out, @yield, @gross_die,@stage, @location,@process_step_type,@vendor_lot)
 					set @date_in =null
 					set @date_out=null
 					set  @qty_in=null
 					set  @qty_out=null
 					set  @yield=null
 					set  @gross_die = null	--Bhaskar added this on 06 Feb 2004
 			
 				fetch next from fabLotCursor into @lot_id, @vendor_lot, @date_in, @date_out, @qty_in, @qty_out, @yield, @gross_die
 				end
 				close fabLotCursor
 				deallocate fabLotCursor
 	
 				fetch next from fabLotCursor1 into @lot_id 
 			end
 			close fabLotCursor1
 			deallocate fabLotCursor1
 
 		
 			if @process_step_type = 'fab' and @process_step_id <> 0 and  @lot_number = '0' and @date_From = '0' and @date_To= '0'
 				select * from @results 
 			if @process_step_type = 'fab' and @process_step_id <> 0  and  @lot_number <> '0' and @date_From = '0' and @date_To= '0'
 				select * from @results where LotNumber=@lot_number 
 
 			if @process_step_type = 'fab' and @process_step_id <> 0  and  @lot_number <> '0' and @date_From <> '0' and @date_To= '0'
 				select * from @results  where LotNumber=@lot_number and Stage = @process_step_type and DateOut >=@begin_DT order by productName
 
 
 			if @process_step_type = 'fab' and @process_step_id <> 0  and  @lot_number <> '0' and @date_From <> '0' and @date_To <> '0'
 				select * from @results  where LotNumber=@lot_number and Stage = @process_step_type and DateOut >=@begin_DT and  DateOut <=@end_DT order by productName
 
 			if @process_step_type = 'fab' and @process_step_id <> 0  and  @lot_number = '0' and @date_From <> '0'  and @date_To= '0'
 				select * from @results  where  Stage = @process_step_type and DateOut >=@begin_DT order by productName
 
 			if @process_step_type = 'fab' and @process_step_id <> 0  and  @lot_number = '0' and @date_From <> '0'  and @date_To<> '0'
 				select * from @results  where  Stage = @process_step_type and DateOut >=@begin_DT and  DateOut <=@end_DT order by productName
 
 
 			if @process_step_type = 'fab' and @process_step_id <> 0  and  @lot_number <> '0' and @date_From = '0' and @date_To <> '0'
 				select * from @results  where  LotNumber=@lot_number and Stage = @process_step_type and DateOut <=@end_DT order by productName
 
 			if @process_step_type = 'fab' and @process_step_id <> 0  and  @lot_number = '0' and @date_From = '0' and @date_To <> '0'
 				select * from @results  where  Stage = @process_step_type and DateOut <=@end_DT order by productName
 			
 		end
 
 		/* else if @process_step_type = 'test'
 		begin
 			
 			if @process_step_id <>'0'
 				select * from fn_yield_all() where Stage = 4 and ProcessStepID = @process_step_id order by productName
 			else
 			
 			select * from fn_yield_all() where Stage = 4 order by productName
 			
 			if @date_From = '0' and @date_To= '0'
 				select * from fn_yield_all() where Stage = 4 order by productName
 			else if @date_From <>'0' and @date_To = '0'
 				select * from fn_yield_all() where Stage = 4 and DateOut >=@date_From order by productName
 			else if @date_From ='0' and @date_To <> '0'
 				select * from fn_yield_all() where Stage = 4 and DateOut <=@date_To order by productName
 			else
 				select * from fn_yield_all() where Stage = 4 and DateOut >=@date_From and DateOut <=@date_To order by productName
 		end*/
 	end
 	
 end
 --select * from @results